﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlServerCe;
using System.Data.Common;

namespace KeepFocused
{
	///
	/// This class represents a database client.
	///
    public class Database
    {
        #region [Private Fields]        
        string connectionString = string.Empty;
        #endregion

        #region [Constructors]
        public Database(string connString)
        {
            this.connectionString = connString;
        }
        #endregion

        #region [Properties]
        public string ConnetionString
        {
            get { return this.connectionString; }
        }
        #endregion

        #region [Session Management]
        /// Creates a new session and returns session ID.
		public int StartNewSession (string sessionDesc)
		{
            using (SqlCeConnection connection = new SqlCeConnection(this.connectionString))
            {
                connection.Open();
                SqlCeCommand command = connection.CreateCommand();
                command.Parameters.Add("@DESC", System.Data.SqlDbType.NVarChar, 200);
                command.Parameters["@DESC"].Value = sessionDesc;

                command.Parameters.AddWithValue("@STATUS", "Ready");
                command.Parameters.Add("@STARTTIME", System.Data.SqlDbType.DateTime);
                command.Parameters["@STARTTIME"].Value = DateTime.Now;

                command.CommandText = "INSERT INTO Sessions(description, start_time, status) VALUES (@DESC, @STARTTIME, @STATUS)";
                int cnt = command.ExecuteNonQuery();
                if (cnt == 1)
                {
                    command.CommandText = "SELECT @@IDENTITY AS ID from Sessions";

                    object temp = command.ExecuteScalar();
                    if (temp != null)
                        return Convert.ToInt32(temp);
                }
            }

            return 0;
		}
		
		public void EndSession (int session_id, string status)
		{
            using (SqlCeConnection connection = new SqlCeConnection(this.connectionString))
            {
                connection.Open();
                SqlCeCommand command = connection.CreateCommand();
                command.Parameters.Add("@STATUS", System.Data.SqlDbType.NVarChar, 100);
                command.Parameters.Add("@ID", System.Data.SqlDbType.Int);
                command.Parameters["@STATUS"].Value = status;
                command.Parameters["@ID"].Value = session_id;

                command.CommandText = "UPDATE Sessions SET status=@STATUS WHERE id=@ID";
                int cnt = command.ExecuteNonQuery();

                if (cnt != 1)
                    throw new Exception("Error occurs when ending a session");
            }
		}
		
        /// <summary>
        /// Returns all sessions including finished and unfinished.
        /// </summary>
        /// <returns></returns>
		public Dictionary<int, string> GetAllSessions()
		{
            Dictionary<int, string> sessions = new Dictionary<int, string>();

            using (SqlCeConnection connection = new SqlCeConnection(this.connectionString))
            {
                connection.Open();
                SqlCeCommand command = connection.CreateCommand();
                command.CommandText = "SELECT id, description FROM Sessions";
                using (SqlCeDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        int session_id = reader.GetInt32(0);
                        string description = reader.GetString(1);

                        sessions.Add(session_id, description);
                    }
                }
            }

            return sessions;
		}

        public Model.Session GetSession(int session_id)
        {
            Model.Session session = null;
            using (SqlCeConnection connection = new SqlCeConnection(this.connectionString))
            {
                connection.Open();
                SqlCeCommand command = connection.CreateCommand();
                command.CommandText = "SELECT id, description, start_time, end_time, status FROM Sessions WHERE id=@ID";

                command.Parameters.AddWithValue("@ID", session_id);

                using (SqlCeDataReader reader = command.ExecuteReader())
                {
                    if (reader.Read())
                    {
                        session = new Model.Session
                        {
                            ID = reader.GetInt32(0),
                            Description = reader.GetString(1),
                            StartTime = reader.GetDateTime(2),
                            EndTime = reader.IsDBNull(3) ? (DateTime?)null : reader.GetDateTime(3),
                            Status = reader.GetString(4)
                        };
                    }
                }
            }

            return session;
        }
		
		/// Returns the unfinished session if it exists. Otherwise
		/// returns 0.
		public int GetUnfinishedSession(out string description)
		{
            description = string.Empty;
            int session_id = 0;

            using (SqlCeConnection connection = new SqlCeConnection(this.connectionString))
            {
                connection.Open();
                SqlCeCommand command = connection.CreateCommand();
                command.CommandText = "SELECT id, description FROM Sessions WHERE status='Ready'";
                using (SqlCeDataReader reader = command.ExecuteReader())
                {
                    // we need to skip those corrupted sessions
                    while (reader.Read())
                    {
                        session_id = reader.GetInt32(0);
                        description = reader.GetString(1);
                    }
                }
            }

            return session_id;
		}

		#endregion
		
		#region [Activity Management]
		public int StartNewActivity (int session_id, string activityDesc)
		{
            using (SqlCeConnection connection = new SqlCeConnection(this.connectionString))
            {
                connection.Open();
                SqlCeCommand command = connection.CreateCommand();

                command.CommandText = "SELECT activity_index FROM ActivityRecords WHERE session_id=@SESSION_ID";
                command.Parameters.AddWithValue("@SESSION_ID", session_id);

                int idx = 0;

                using (SqlCeDataReader reader = command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        int activity_index = reader.GetInt32(0);
                        idx = Math.Max(idx, activity_index);
                    }
                }

                // session has ended because it contains 4 activities already.
                if (idx == 4)
                    return 0;

                idx++;

                command.Parameters.Clear();
                command.CommandText = "INSERT INTO ActivityRecords (start_time, comments, session_id, activity_index, status) VALUES (@START_TIME, @DESCRIPTION, @SESSION_ID, @ACTIVITY_INDEX, @STATUS)";
                command.Parameters.AddWithValue("@START_TIME", DateTime.Now);
                command.Parameters.AddWithValue("@DESCRIPTION", activityDesc);
                command.Parameters.AddWithValue("@SESSION_ID", session_id);
                command.Parameters.AddWithValue("@ACTIVITY_INDEX", idx);
                command.Parameters.AddWithValue("@STATUS", "Ongoing");

                int cnt = command.ExecuteNonQuery();
                if (cnt == 1)
                {
                    command.CommandText = " SELECT @@IDENTITY AS ID from ActivityRecords";
                    object activity_id = command.ExecuteScalar();
                    return Convert.ToInt32(activity_id);
                }
            }
            return 0;
		}
		
        /// <summary>
        /// Ends an activity. 
        /// </summary>
        /// <param name="session_id"></param>
        /// <param name="activity_id"></param>
        /// <param name="status">Status of the activity. It will be successful or aborted.</param>
		public void EndActivity (int session_id, int activity_id, string status)
		{
            using (SqlCeConnection connection = new SqlCeConnection())
            {
                connection.Open();

                SqlCeCommand command = connection.CreateCommand();
                command.CommandText = "UPDATE ActivityRecords SET status=@STATUS WHERE session_id=@SESSION_ID AND id=@ACTIVITY_ID";
                command.Parameters.AddWithValue("@STATUS", status);
                command.Parameters.AddWithValue("@SESSION_ID", session_id);
                command.Parameters.AddWithValue("@ACTIVITY_ID", activity_id);

                command.ExecuteNonQuery();
            }
		}

        public Model.Activity GetActivity(int session_id, int activity_id)
        {
            Model.Activity activity = null;

            using (SqlCeConnection connection = new SqlCeConnection(this.connectionString))
            {
                connection.Open();
                SqlCeCommand command = connection.CreateCommand();
                command.CommandText = "SELECT description, start_time, end_time, status, activity_index FROM ActivityRecords WHERE id=@ID AND session_id=@SESSIONID";
                command.Parameters.AddWithValue("@ID", activity_id);
                command.Parameters.AddWithValue("@SESSIONID", session_id);
                using (SqlCeDataReader reader = command.ExecuteReader()){
                    while (reader.Read())
                    {
                        activity = new Model.Activity
                        {
                            ID = activity_id,
                            ActivityIndex = reader.GetInt32(4),
                            Comments = reader.GetString(0),
                            StartTime = reader.GetDateTime(1),
                            EndTime = reader.IsDBNull(2) ? (DateTime?)null : reader.GetDateTime(2)
                        };
                    }
                }
            }

            return activity;
        }
		#endregion
    }
}
